﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAO;
using System.Data.SqlClient;
using System.Data;

namespace APA_DAO
{
    public class ChiTieuDao
    {
        public ChiTieuDao()
        {
            new SqlHelper();
        }

        public DataTable getLoaiChi(int id)
        {
            return SqlHelper.ExecuteDataTable("SP_GET_LOAI_CHI", CommandType.StoredProcedure,
                "@ID", id);
        }

        public DataRow getChiTieuById(int id)
        {
            DataTable table = SqlHelper.ExecuteDataTable("SP_GET_CHI_TIEU_BY_ID", CommandType.StoredProcedure,
                "@ID", id);
            if (table != null && table.Rows.Count > 0)
            {
                return table.Rows[0];
            }
            return null;
        }


        public bool deleteChiTieu(int id)
        {
            return SqlHelper.ExecuteNonQuery("SP_DELETE_CHI_TIEU", CommandType.StoredProcedure,
                "@ID", id,
                "@USR_ID", LoginDao.userID);
        }

        public bool updateChiTieu(int id, string mucDich, float tongTien, DateTime? ngayChi, int loaiChi, int soThuePhong, string notes)
        {
            try
            {
                SqlTransaction sqlTransaction;
                SqlConnection con = new SqlConnection(SqlHelper.ConnectString);
                con.Open();
                sqlTransaction = con.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    if (!SqlHelper.ExecuteNonQueryWithTranSaction("SP_UPDATE_CHI_TIEU", sqlTransaction, con,
                        "@ID", id,
                        "@MUC_DICH", mucDich,
                        "@SO_TIEN", tongTien,
                        "@NGAY_CHI", ngayChi,
                        "@LOAI_CHI", loaiChi,
                        "@SO_THUE_PHONG", soThuePhong,
                        "@NOTES", notes,
                        "@USR_ID", LoginDao.userID))
                    {
                        sqlTransaction.Rollback();
                        return false;
                    }

                    sqlTransaction.Commit();
                    return true;
                }
                catch (Exception e)
                {
                    sqlTransaction.Rollback();
                    Console.WriteLine(e);
                    return false;
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
                return false;
            }
        }
        public DataTable getChiTieu(int nguoiChi, DateTime? fromDate, DateTime? toDate, int soThuePhong, int tinhTrang, int loaiChi)
        {
            return SqlHelper.ExecuteDataTable("SP_GET_CHI_TIEU", CommandType.StoredProcedure,
                "@NGUOI_CHI", nguoiChi,
                "@NGAY_CHI_FROM", fromDate,
                "@NGAY_CHI_TO", toDate,
                "@LOAI_CHI", loaiChi,
                "@SO_THUE_PHONG", soThuePhong,
                "@TINH_TRANG", tinhTrang);
        }
        public bool insertChiTieu(string mucDich, float soTien, DateTime ngayChi, int soThuePhong, int loaiChi, string notes)
        {
            try
            {
                SqlTransaction sqlTransaction;
                SqlConnection con = new SqlConnection(SqlHelper.ConnectString);
                con.Open();
                sqlTransaction = con.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    if (!SqlHelper.ExecuteNonQueryWithTranSaction("SP_INSERT_CHI_TIEU", sqlTransaction, con,
                        "@MUC_DICH", mucDich,
                        "@SO_TIEN", soTien,
                        "@NGUOI_CHI", LoginDao.userID,
                        "@NGAY_CHI", ngayChi,
                        "@SO_THUE_PHONG", soThuePhong,
                        "@LOAI_CHI", loaiChi,
                        "@NOTES", notes,
                        "@USR_ID", LoginDao.userID))
                    {
                        sqlTransaction.Rollback();
                        return false;
                    }

                    sqlTransaction.Commit();
                    return true;
                }
                catch (Exception e)
                {
                    sqlTransaction.Rollback();
                    Console.WriteLine(e);
                    return false;
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
                return false;
            }
        }
    }
}
